if object_id('BIMonitor.SSISProblemSummaryReportZeroRows') is not null
  drop procedure BIMonitor.SSISProblemSummaryReportZeroRows;
go

create procedure BIMonitor.SSISProblemSummaryReportZeroRows
  @Environment varchar(75)

as

set nocount on

--create variables
declare @Counter               int,
        @RowCount              int,
        @ProblemType           varchar(10),
        @SSRSProblemTypeSort   smallint;

declare @ProblemTypes table
 (ProblemType           varchar(10) primary key,
  SSRSProblemTypeSort   smallint not null,
  Counter               int identity(1,1));


--set variables
set @Counter = 1;


--load problem types table variable
insert into @ProblemTypes values('Failure',1);  --the list of problem types may be extended, if needed
insert into @ProblemTypes values('Error',2);
insert into @ProblemTypes values('Warning',3);


--loop over all problem types and load "zero" rows if no problem type row exists
while @Counter <= (select count(1) from @ProblemTypes)
  begin


    --set the problem type and SSRS problem type sort variables for loop processing
    select @ProblemType=ProblemType,@SSRSProblemTypeSort=SSRSProblemTypeSort from @ProblemTypes where Counter=@Counter


    --determine if a problem type row exists.  number of rows should always be either 0 or 1
    set @RowCount = (select count(1) from BIMonitor.SSISProblemSummary where Environment=@Environment and ProblemType=@ProblemType)


    --if no problem type row exists, add a "zero" row
    if @RowCount = 0
      begin
        insert into BIMonitor.SSISProblemSummary
         (Environment,ProblemType,SSRSProblemTypeSort,LT1Count,[1_2Count],[3_6Count],[7_12Count],[13_24Count])
        values
         (@Environment,@ProblemType,@SSRSProblemTypeSort,0,0,0,0,0)
      end


    --increment while loop counter
    set @Counter = @Counter + 1
    continue

  end

set nocount off